package com.base.myproject.client.HR.Training;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.base.myproject.client.busi.BaseStore;
import com.base.myproject.client.busi.BaseTreeStore;
import com.base.myproject.client.busi.BO.BusiObject;
import com.base.myproject.client.busi.BO.TreeNodeModelData;
import com.base.myproject.client.jdbc.DataSet;
import com.base.myproject.client.jdbc.StoreInfo;
import com.base.myproject.client.system.BaseResource;
import com.base.myproject.client.system.Env;
import com.base.myproject.client.tools.CommandSyncContainer;
import com.base.myproject.client.tools.CommandSyncReturnObject;
import com.base.myproject.client.tools.CommandSyncsql;
import com.base.myproject.client.tools.GreetingService;
import com.base.myproject.client.tools.GreetingServiceAsync;
import com.base.myproject.client.ui.BaseFormPanel;
import com.base.myproject.client.ui.BaseGrid;
import com.base.myproject.client.ui.BaseJDBCPanel;
import com.base.myproject.client.ui.celleditor.BaseComboBoxForm;
import com.extjs.gxt.ui.client.Style.HorizontalAlignment;
import com.extjs.gxt.ui.client.Style.LayoutRegion;
import com.extjs.gxt.ui.client.Style.SelectionMode;
import com.extjs.gxt.ui.client.Style.VerticalAlignment;
import com.extjs.gxt.ui.client.data.BaseModelData;
import com.extjs.gxt.ui.client.data.ModelType;
import com.extjs.gxt.ui.client.event.BaseEvent;
import com.extjs.gxt.ui.client.event.Events;
import com.extjs.gxt.ui.client.event.Listener;
import com.extjs.gxt.ui.client.event.MessageBoxEvent;
import com.extjs.gxt.ui.client.event.TreePanelEvent;
import com.extjs.gxt.ui.client.store.Store;
import com.extjs.gxt.ui.client.util.Margins;
import com.extjs.gxt.ui.client.widget.ContentPanel;
import com.extjs.gxt.ui.client.widget.Dialog;
import com.extjs.gxt.ui.client.widget.Info;
import com.extjs.gxt.ui.client.widget.MessageBox;
import com.extjs.gxt.ui.client.widget.button.Button;
import com.extjs.gxt.ui.client.widget.form.DateField;
import com.extjs.gxt.ui.client.widget.form.FieldSet;
import com.extjs.gxt.ui.client.widget.form.NumberField;
import com.extjs.gxt.ui.client.widget.form.StoreFilterField;
import com.extjs.gxt.ui.client.widget.form.TextArea;
import com.extjs.gxt.ui.client.widget.form.TextField;
import com.extjs.gxt.ui.client.widget.grid.CellEditor;
import com.extjs.gxt.ui.client.widget.grid.CheckBoxSelectionModel;
import com.extjs.gxt.ui.client.widget.grid.ColumnConfig;
import com.extjs.gxt.ui.client.widget.grid.ColumnModel;
import com.extjs.gxt.ui.client.widget.layout.BorderLayoutData;
import com.extjs.gxt.ui.client.widget.layout.TableData;
import com.extjs.gxt.ui.client.widget.layout.TableLayout;
import com.extjs.gxt.ui.client.widget.toolbar.PagingToolBar;
import com.extjs.gxt.ui.client.widget.treepanel.TreePanel;
import com.extjs.gxt.ui.client.widget.treepanel.TreePanelSelectionModel;
import com.extjs.gxt.ui.client.widget.treepanel.TreePanel.CheckCascade;
import com.extjs.gxt.ui.client.widget.treepanel.TreePanel.CheckNodes;
import com.google.gwt.core.client.GWT;
import com.google.gwt.i18n.client.DateTimeFormat;
import com.google.gwt.i18n.client.NumberFormat;
import com.google.gwt.user.client.rpc.AsyncCallback;
import com.google.gwt.user.client.ui.AbstractImagePrototype;
import com.google.gwt.user.client.ui.HorizontalPanel;
import com.google.gwt.user.client.ui.Label;
import com.google.gwt.user.client.ui.VerticalPanel;
/**
 * 部门培训回顾
 * @author Administrator
 *
 */
public class Training_Result extends BaseJDBCPanel {
	String tree_depcode = null;//讲当前树形结构中选中的部门id保存
	String tree_depname = null;
	
	TreePanel<TreeNodeModelData> treePanel = null;
	// 创建树形结构的数据表
	BaseTreeStore<TreeNodeModelData> treeStore = null;
	StoreFilterField<BusiObject> ajaxfield;//类似ajax的自动查询框
	DateField tool_date  = new DateField();
	Button to_do = new Button ("生成数据");
	BaseComboBoxForm<BaseModelData> sectionCode = new BaseComboBoxForm<BaseModelData>("id","name","SystemInfo","where len(id)=(select max(len(id)) from systeminfo)",true);
	String results = null;
	String bcode = Env.getEnvbcode();
	PagingToolBar toolBar1 = null;
	PagingToolBar toolBar2 = null;
	
	public Training_Result(){
		String sql = "select substring('id like '''+replace(permissionsbcode,';','%'' or id like '''),9," +
		"len('id like '''+replace(permissionsbcode,';','%'' or id like '''))-21) as result from" +
		" busipermissions where bcode = '"+bcode+"' and busicode = 8010";
		final com.base.myproject.client.tools.GreetingServiceAsync greetingService2 = GWT.create(com.base.myproject.client.tools.GreetingService.class);
		greetingService2.getDataSet(sql, new AsyncCallback<DataSet>(){
		@Override
			public void onFailure(Throwable caught) {	
			}
			@Override
			public void onSuccess(DataSet result) {  
			   results = result.getValue(0, "result");
			   init();
			}
		});
		
	}
	public void init(){
		to_do.addListener(Events.OnClick, getListener());
		initwidget();
	    registerMainStore(mainstore);
		registerStore(mainstore);
		registerStore(store);
		tool_date.getPropertyEditor().setFormat(DateTimeFormat.getFormat("yyyy-MM"));
	}
	public void complete()
	{
		super.complete();
		getToolbarll().add(tool_date);
		getToolbarll().add(to_do);
	}
	private void initwidget() {
		toolBar1 = new PagingToolBar(35,true);
		toolBar2 = new PagingToolBar(7);
		HorizontalPanel hp=new HorizontalPanel();
		hp.setSpacing(1);
		
		hp.add(getTreePanel());

		
		FieldSet fs = new FieldSet();
		fs.setStyleName("fieldset_css2");
		fs.setHeading("查询操作");
		fs.add(cread_field());
		
		BorderLayoutData northData = new BorderLayoutData(LayoutRegion.WEST, 200);  
		northData.setCollapsible(true);  
		northData.setFloatable(true);  
		northData.setHideCollapseTool(true);  
		northData.setSplit(true);  
		northData.setMargins(new Margins(5, 5, 0, 0));
		
		BorderLayoutData centerData = new BorderLayoutData(LayoutRegion.CENTER);  
	    centerData.setMargins(new Margins(5,0,0,0));  
	    
		VerticalPanel vpp = new VerticalPanel();
		vpp.add(fs);
		HorizontalPanel hpp = new HorizontalPanel();
		
		VerticalPanel vp = new VerticalPanel();
		ContentPanel panel3 = new ContentPanel();
		panel3.setHeaderVisible(false);
		panel3.add(createGrid());
		//panel3.setBottomComponent(toolBar2);
		vp.add(panel3);
		vp.add(createForm2());
		
		hpp.add(createmiangrid());
		hpp.add(vp);
		hpp.setSpacing(3);
		vpp.add(hpp);
		
		ContentPanel center = new ContentPanel();
		center.setHeaderVisible(false);
		center.add(vpp);
		ContentPanel panel = new ContentPanel();
		panel.setHeading("选择部门");
		panel.setCollapsible(true);
		panel.add(getTreePanel());
		add(panel,northData);
		add(center,centerData);
	    this.layout();
	}
	TreePanel<TreeNodeModelData> getTreePanel() {
		if (treePanel == null) {
			initTreePanel();
		}
		return treePanel;
	}
	BaseTreeStore<TreeNodeModelData> getTreeStore() {
		if (treeStore == null) {
			initTreeStore();
		}
		return treeStore;
	}
	BaseTreeStore<TreeNodeModelData> initTreeStore() {
		String sql2 = "select icode,id,name from SystemInfo where id like "+results+" order by id asc";
		StoreInfo si = new StoreInfo();
		si.setSql(sql2);
		si.setTreesortcolumn("id");
		si.setKeyname(new String[] { "icode" });
		si.setTablename("SystemInfo");
		treeStore = new BaseTreeStore<TreeNodeModelData>(si, false);
		treeStore.load();
		return treeStore;
	}
	// 实例化TreePanel
	TreePanelSelectionModel<TreeNodeModelData> tpsm = new TreePanelSelectionModel<TreeNodeModelData>();
	void initTreePanel() {
		//super.onRender();  
		treePanel = new TreePanel<TreeNodeModelData>(getTreeStore());
		treePanel.setWidth(200);
		treePanel.setBorders(true);
		treePanel.setHeight(506);
		treePanel.setDisplayProperty("name");
		treePanel.setSelectionModel(tpsm);
		treePanel.setCheckStyle(CheckCascade.PARENTS); 
		treePanel.setCheckNodes(CheckNodes.BOTH);  
		treePanel.setCheckable(false);
		treePanel.setAutoLoad(true);
		treePanel.getStyle().setLeafIcon(AbstractImagePrototype.create(BaseResource.INSTANCE
				.tree_file())); 
		treePanel.addListener(Events.OnClick, new Listener<TreePanelEvent<TreeNodeModelData>>() {   
			public void handleEvent(TreePanelEvent<TreeNodeModelData> be) {   
				final String id=be.getItem().get("id").toString();//部门id
				final String name = be.getItem().get("name").toString();
				tree_depcode=id;
				if(!id.equals("00")){//表示为主节点 不因有任何操作
					 String sql="select  * from systeminfo where len('"+id+"')=(select len(max(id)) from  systeminfo)";
					final com.base.myproject.client.tools.GreetingServiceAsync greetingService2 = GWT.create(com.base.myproject.client.tools.GreetingService.class);
					   greetingService2.getDataSet(sql, new AsyncCallback<DataSet>(){
						@Override
						public void onFailure(Throwable caught) {	
						}
						@Override
						 public void onSuccess(DataSet result) {  
			   			   if(result.getRowCount()>0){
			   				  set_grid(id);           
			   				  //buser.setFilter(" WHERE systemid like '"+tree_depcode+"%25' ");
			   			   }
			   					   
						  }
					     });	   
				 }
				
	  		} 
		    });   
	}
	
	public void set_grid(String id){
		StoreInfo info2 = new StoreInfo();
		info2.setTablename("hr_TrainingResult");
		info2.setKeyname(new String[] { "icode" });
		String where=" where 1=1 and ";
		if(id.length()<5){
			where=where+" sectionCode like '" +id+ "%25'";
		}else{
			where=where+" sectionCode = '" +id+ "'";
		}
		info2.setSql("select * from hr_TrainingResult "+where);//这里sql的linke %%  有问题
		miangrid.getStore().setStoreinfo(info2);
		miangrid.getStore().reload();
		miangrid.getView().refresh(true);
    }
	
	/**********************************/
	BaseStore<BusiObject> mainstore = null;
	private BaseStore<BusiObject> getMainStore(){
		if(mainstore==null) {
			createmainstore();
		}
		mainstore.setToolBar(toolBar1);
		mainstore.initcompleted();
		return mainstore;
	}
	ModelType mtype;
	private BaseStore<BusiObject> createmainstore(){
		mtype = new ModelType();
		mtype.setRoot("records");
		mtype.setRecordName("record");
		mtype.setTotalName("totle");
		mtype.addField("icode");
		mtype.getField("icode").setType(Long.class);
		mtype.addField("YearMonth");
		mtype.getField("YearMonth").setType(Date.class);
		mtype.addField("sectionCode");
		mtype.getField("sectionCode").setType(String.class);
		mtype.addField("Overall");
		mtype.getField("Overall").setType(String.class);
		mtype.addField("TrainingOfficer");
		mtype.getField("TrainingOfficer").setType(String.class);
		mtype.addField("totalPoints");
		mtype.getField("totalPoints").setType(Double.class);
		
		StoreInfo info = new StoreInfo();
		info.setTablename("hr_TrainingResult");
		info.setKeyname(new String[] { "icode"});
		info.setSql("select * from hr_TrainingResult where 1=2");
		mainstore = new BaseStore<BusiObject>(mtype, info);
		return mainstore;
	}
	BaseGrid miangrid=null;
	private BaseGrid createmiangrid(){
		CheckBoxSelectionModel cbs = new CheckBoxSelectionModel();
		List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
		columns.add(cbs.getColumn());
	
	
		ColumnConfig C2 = new ColumnConfig("YearMonth", "年月", 100);
		C2.setDateTimeFormat(DateTimeFormat.getFormat("yyyy-MM"));
		columns.add(C2);
		ColumnModel cm = new ColumnModel(columns);
		miangrid = new BaseGrid(getMainStore(), cm,false,false);
		cbs.setSelectionMode(SelectionMode.SINGLE);
		miangrid.setSelectionModel(cbs);
		miangrid.setHeight(410);
		miangrid.setWidth(165);
		miangrid.setLoadMask(true);
		return miangrid ;
	}
	
	
	BaseFormPanel form2  =null;
	TextField<String> C_num3 = null;
	NumberField C_num4 = null;
	Button btnAccount = null;
    BaseFormPanel createForm2(){
	    form2 = new BaseFormPanel(getMainStore(),false);
	    TableLayout tl = new TableLayout(8);
	
	    TableData td = new TableData();
	    td.setRowspan(1);
	    td.setHorizontalAlign(HorizontalAlignment.LEFT);
	    td.setVerticalAlign(VerticalAlignment.TOP);
	    td.setPadding(2);
	    
	    TableData td2 = new TableData();
	    td2.setRowspan(1);
	    td2.setColspan(7);
	    td2.setHorizontalAlign(HorizontalAlignment.LEFT);
	    td2.setVerticalAlign(VerticalAlignment.TOP);
	    td2.setPadding(2);
	    
	    
	    form2.setLayout(tl);
	    form2.setHeaderVisible(false);
	    form2.setPadding(4);
	
	    form2.add(new Label("月份:"),td);
	    DateField C_data2  = new DateField(); 
	    C_data2.setReadOnly(true);
	    C_data2.setWidth(100);
	    C_data2.getPropertyEditor().setFormat(DateTimeFormat.getFormat("yyyy-MM")); 
	    C_data2.setName("YearMonth");
	    form2.add(C_data2,td);
	    
	    form2.add(new Label("分部门:"),td);
	    sectionCode.setWidth(100);
	    sectionCode.setName("sectionCode");
	    sectionCode.setReadOnly(true);
	    form2.add(sectionCode,td);
	    
	    btnAccount = new Button("计算总分");
	    form2.add(btnAccount,td);
	    btnAccount.setWidth(80);
	    btnAccount.addListener(Events.OnClick, getListener());
	    C_num4  = new NumberField();
	    C_num4.setWidth(40);
	    C_num4.setReadOnly(true);
	    C_num4.setName("totalPoints");
	    C_num4.setFormat(NumberFormat.getPercentFormat());
	    form2.add(C_num4,td);
	    
	    form2.add(new Label("培训主任:"),td);
	    TextField<String> C_num2  = new TextField<String>(); 
	    C_num2.setWidth(100);
	    C_num2.setName("TrainingOfficer");
	    form2.add(C_num2,td);
	    
	    form2.add(new Label("意见:"),td);
	    TextArea  t6 =new TextArea();
	    t6.setWidth(540);
		t6.setHeight(120);
	    t6.setName("Overall");
	    form2.add(t6,td2);
	    
	    form2.inited(); 
	    return form2;
    };
	
	
	
	/*************************/
	BaseStore<BusiObject> store = null;
	BaseStore<BusiObject> getStore(){
		if(store==null) 
		{
			createStore();
		}
		//store.setToolBar(toolBar2);
		//store.initcompleted();
		return store;
	}
	private BaseStore<BusiObject> createStore(){
		ModelType type = new ModelType();
		type.setRoot("records");
		type.setRecordName("record");
		type.setTotalName("totle");
	
		type.addField("micode");
		type.getField("micode").setType(Long.class);
		type.addField("Assessment");
		type.getField("Assessment").setType(String.class);
		type.addField("Top_score");
		type.getField("Top_score").setType(Integer.class);
		type.addField("score");
		type.getField("score").setType(Double.class);
		type.addField("Comments");
		type.getField("Comments").setType(String.class);
		StoreInfo info = new StoreInfo();
		info.setTablename("hr_TrainingResult_list");
		info.setKeyname(new String[] { "icode"});
		info.setSql("select * from hr_TrainingResult_list where micode='{icode}'");
		info.setParent(mainstore);// 关联主表
		info.setLinkparentcolumns(new String[] { "icode" }); // 关联主表字段
		info.setLinkparent_chile_columns(new String[] { "micode" });// 关联主表字段
		store = new BaseStore<BusiObject>(type, info);
		return store;
	}
	
	
	BaseGrid grid=null;
	private BaseGrid createGrid(){
		CheckBoxSelectionModel cbs = new CheckBoxSelectionModel();
		List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
		columns.add(cbs.getColumn());
	
		ColumnConfig C3 = new ColumnConfig("Assessment", "评估项目", 180);
		//BaseComboBoxForm<BaseModelData> bsb2 = new BaseComboBoxForm<BaseModelData>("bcode","bname","bcode","",true);
		//C3.setRenderer(bsb2);
		//C3.setEditor(bsb2.getCelEditor());
		columns.add(C3);
		ColumnConfig C4 = new ColumnConfig("Top_score", "满分", 70);
		columns.add(C4);
		ColumnConfig C5 = new ColumnConfig("score", "得分", 70);
		columns.add(C5);
		ColumnConfig C6 = new ColumnConfig("Comments", "意见", 377);
		TextField C_num6= new TextField<String>(); 
		C_num6.setFireChangeEventOnSetValue(true);
		C_num6.setName("Comments");
		C6.setEditor(new CellEditor(C_num6));
		columns.add(C6);
	
		ColumnModel cm = new ColumnModel(columns);
		grid = new BaseGrid(getStore(), cm,false,false);
		cbs.setSelectionMode(SelectionMode.SINGLE);
		grid.setSelectionModel(cbs);
		grid.setWidth(723);
		grid.setHeight(220);
		grid.setLoadMask(true);
		grid.setBorders(false);
		return grid ;
	}
	
	/**
	 * 过滤
	 * @param store
	 * @param parent
	 * @param record
	 * @param property
	 * @param filter
	 * @return  ajax 查询
	 */
	protected boolean Select(Store<BusiObject> store,
			BusiObject parent, BusiObject record, String property,
			String filter) {
		for (int i = 0; i < mtype.getFieldCount(); i++) {
			Object name = record.get(mtype.getField(i).getName());	
			//TODO
			if (name!=null&&name.toString().indexOf(filter.toLowerCase()) != -1) {
				return true;
			}
		}
		return false;
	}	
	StoreFilterField cread_field(){
		ajaxfield = new StoreFilterField<BusiObject>() {
			@Override
			protected boolean doSelect(Store<BusiObject> store,
					BusiObject parent, BusiObject record, String property,
					String filter) {

			return	Select(store,parent,record,property,filter);
			}

			@Override
			protected void onFilter() {
				super.onFilter();

				// grid.getSelectionModel().select(0);
			}

		};
		ajaxfield.setAutoWidth(true);
		ajaxfield.setToolTip("快速查找");
		ajaxfield.bind(getMainStore());
		this.ajaxfield=ajaxfield;
		return ajaxfield;
	}
	/************************************/
	
	int sync = 0;                                                      
	Listener<BaseEvent> defaultlistener;   
	String time = "";
                                                                     
	public Listener<BaseEvent> getListener() {                                
		if(defaultlistener!=null)                                        
		{                                                                
			return defaultlistener;                                        
		}                                                                
                                                                     
		defaultlistener = new Listener<BaseEvent>() {                    
			public void handleEvent(BaseEvent be) {                        
				sync++;                                                    
				try {                                                      
					if (sync == 1) {                                   
						if (be.getType() == Events.Select) { 
							
						}else if(be.getType() == Events.OnBlur){
							
						}else if(be.getType() == Events.OnClick){
							if(be.getSource().equals(to_do)){
								final Date to_dotime = tool_date.getValue();
								if(to_dotime == null){
									MessageBox.alert("提示", "请选择时间！", null); 
								}else if(tree_depcode == null){
									MessageBox.alert("提示", "请选择分部门！", null);
								}else{
									int year = to_dotime.getYear() + 1900;
									int month = to_dotime.getMonth() + 1;
									if(month < 10){
										time = year + "-" + 0 + month;
									}else{
										time = year + "-" + month;
									}
									if(mainstore.getCount() == 0){
										createData(tree_depcode,time);
									}else{
										//这里有BUG，因为现在只判断选中的行的月数会不会重复
										Date currentTime = (Date)mainstore.getValue("YearMonth");
										String times = null;
										int years = currentTime.getYear() + 1900;
										int months = currentTime.getMonth() + 1;
										if(months < 10){
											times = years + "-" + 0 + months;
										}else{
											times = years + "-" + months;
										}
										if(times.equals(time)){
											final MessageBox confirm = MessageBox.confirm("提示", "当前部门已存在所选择月份的记录,是否覆盖？",null);
											confirm.addCallback(new Listener<MessageBoxEvent>(){
												@Override
												public void handleEvent(MessageBoxEvent be) {
													Button clicked = be.getButtonClicked();
													if (clicked.getItemId().equals(Dialog.YES)){
														String sql = "delete from hr_TrainingResult where substring(convert(char(20),YearMonth,121),0,8) = '"+time+"'";
														command(sql);
														mainstore.reload();
														createData(tree_depcode,time);
													}
												}
												
											});
										}else{
											createData(tree_depcode,time);
										}
									}
								}
							}else if(be.getSource().equals(btnAccount)){
								int rows = grid.getStore().getCount();
								Date currentTime = (Date)mainstore.getValue("YearMonth");
								int years = currentTime.getYear() + 1900;
								int months = currentTime.getMonth() + 1;
								if(months < 10){
									time = years + "-" + 0 + months;
								}else{
									time = years + "-" + months;
								}
								float scores = 0;
								float topscores = 0;
								float results = 0;
								for(int i = 0;i < rows;i++){
									scores += Float.parseFloat(grid.getValue(i, "score").toString());
									topscores += Float.parseFloat(grid.getValue(i,"Top_score").toString());
								}
								if(scores == 0){
									results = 0;
								}else{
									results = scores/topscores;
								}
								String sql = "update hr_TrainingResult set totalPoints='"+results+"' where sectioncode='"+tree_depcode+"' and substring(convert(char(20),YearMonth,121),0,8) = '"+time+"'";
								command(sql);
								mainstore.reload();
							}
						}                                          
					}                                                  
				} finally {                                                
					sync--;                                            
				}                                                          
			}                                                              
		};                                                               
		return defaultlistener;                                          
	}
	
	public void percentageAttendence(String typecode,String keydate,String sectioncode,String tooldate,final int topscore,final String typename){
		String sql = "";
		if(keydate.equals("入职日期")){
			sql = "if (select count(icode) from hr_trainingRecord_bcode_content where  typeCode = " +
					"(select distinct typecode from hr_trainingRecord_bcode where substring(convert(char(20),entryDate,121),0,8)" +
					" = '"+tooldate+"' and sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) <> 0 " + 
					"begin select round(attendenced/totals,2)as result from (select" +
					"(select cast(count(icode) as float) from hr_trainingRecord_bcode_content where attendanced = 1 and typeCode = (" + 
					"select distinct typecode from hr_trainingRecord_bcode where substring(convert(char(20),entryDate,121),0,8) = '"+tooldate+"'" +
					" and sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) as attendenced," +
					"(select count(icode) from hr_trainingRecord_bcode_content where  typeCode = (" +
					"select distinct typecode from hr_trainingRecord_bcode where substring(convert(char(20),entryDate,121),0,8) = '"+tooldate+"' " +
					"and sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) as totals) as trainingRecord end";
		}else{
			sql = "if (select count(icode) from hr_trainingRecord_bcode_content where " +
					"substring(convert(char(20),trainingDate,121),0,8)='"+tooldate+"' and typeCode = " +
					"(select distinct typecode from hr_trainingRecord_bcode where " +
					"sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) <> 0 " + 
					"begin select round(attendenced/totals,2)as result from (select" +
					"(select cast(count(icode) as float) from hr_trainingRecord_bcode_content where attendanced = 1 and " +
					"substring(convert(char(20),trainingDate,121),0,8)='"+tooldate+"'and typeCode = (" + 
					"select distinct typecode from hr_trainingRecord_bcode where substring(convert(char(20),trainingDate,121),0,8) = '"+tooldate+"'" +
					" and sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) as attendenced," +
					"(select count(icode) from hr_trainingRecord_bcode_content where " +
					"substring(convert(char(20),trainingDate,121),0,8)='"+tooldate+"' and typeCode = (" +
					"select distinct typecode from hr_trainingRecord_bcode where substring(convert(char(20),trainingDate,121),0,8) = '"+tooldate+"' " +
					"and sectionCode = '"+sectioncode+"' and typeCode = '"+typecode+"')) as totals) as trainingRecord end";
		}
		System.out.println("***************************"+ sql);
		GreetingServiceAsync greetingservice = GWT.create(GreetingService.class);
		greetingservice.getDataSet(sql, new AsyncCallback<DataSet>(){
			@Override
			public void onFailure(Throwable caught) {
				MessageBox.alert("Error"," 服务器繁忙......", null);
			}
			@Override
			public void onSuccess(DataSet result) {
				if(result.getRowCount() != 0){
					String str = result.getValue(0, "result");
					if(str == null){
						BusiObject bo = new BusiObject();
						bo.set("Assessment", typename);
						bo.set("Top_score", 0);
						bo.set("score", 0);
						store.add(bo);
					}else{
						float score = Float.parseFloat(result.getValue(0, "result"));
						if(score == 0){
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", 0);
							bo.set("score", score);
							store.add(bo);
						}else{
							float scores = score * topscore;
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", topscore);
							bo.set("score", scores);
							store.add(bo);
						}
					}
				}else{
					BusiObject bo = new BusiObject();
					bo.set("Assessment", typename);
					bo.set("Top_score", 0);
					bo.set("score", 0);
					store.add(bo);
				}
				
			}
		});
	}
	
	public void percentageComplete(String keydate,String sectioncode,String typecode,final int topscore,final String typename,String tooldate){
		String sql = "";
		if(keydate.equals("入职日期")){
			sql = "select sum(grade) as result,count(icode) as totals from hr_trainingRecord_bcode where " +
					"typecode = (select distinct typecode from hr_trainingRecord_bcode where sectionCode='"+sectioncode+"' " +
					"and typeCode='"+typecode+"') and substring(convert(char(20),entryDate,121),0,8)='"+tooldate+"'";
		}else{
			sql = "select sum(grade) as result,count(icode) as totals from hr_trainingRecord_bcode where " +
					"typecode = (select distinct typecode from hr_trainingRecord_bcode_content where  " +
					"typeCode='"+typecode+"' and substring(convert(char(20),trainingDate,121),0,8)='"+tooldate+"') and sectioncode = '"+sectioncode+"'";
		}
		GreetingServiceAsync greetingservice = GWT.create(GreetingService.class);
		greetingservice.getDataSet(sql, new AsyncCallback<DataSet>(){
			@Override
			public void onFailure(Throwable caught) {
				MessageBox.alert("Error"," 服务器繁忙......", null);
			}
			@Override
			public void onSuccess(DataSet result) {	
				if(result.getRowCount() != 0){
					try{
						String str = result.getValue(0, "result");
						String strs = result.getValue(0, "totals");
						if(str == null || strs == null){
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", 0);
							bo.set("score", 0);
							store.add(bo);
						}else{
							float score = Float.parseFloat(result.getValue(0, "result"));
							int totals = Integer.parseInt(result.getValue(0, "totals"));
							float scores = score/totals*topscore;
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", topscore);
							bo.set("score", scores);
							store.add(bo);
						}
					}catch(Exception e){
						e.printStackTrace();
					}
				}else{
					BusiObject bo = new BusiObject();
					bo.set("Assessment", typename);
					bo.set("Top_score", 0);
					bo.set("score", 0);
					store.add(bo);
				}
			}
		});
	}
	
	public void score(String keydate,String sectioncode,String typecode,final int topscore,final String typename,String tooldate){
		String sql = "";
		if(keydate.equals("入职日期")){
			sql = "select sum(grade) from hr_trainingRecord_bcode_content where typecode = " +
					"(select distinct typecode from hr_trainingRecord_bcode where sectionCode='"+sectioncode+"' " +
					"and typeCode='"+typecode+"' and substring(convert(char(20),entryDate,121),0,8)='"+tooldate+"')";
		}else{
			sql = "select sum(grade) as result from hr_trainingRecord_bcode_content where typecode = " +
			"(select distinct typecode from hr_trainingRecord_bcode where sectionCode='"+sectioncode+"' " +
			"and typeCode='"+typecode+"' and substring(convert(char(20),trainingDate,121),0,8)='"+tooldate+"')";
		}
		GreetingServiceAsync greetingservice = GWT.create(GreetingService.class);
		greetingservice.getDataSet(sql, new AsyncCallback<DataSet>(){
			@Override
			public void onFailure(Throwable caught) {
				MessageBox.alert("Error"," 服务器繁忙......", null);
			}
			@Override
			public void onSuccess(DataSet result) {	
				if(result.getRowCount() != 0){
					String str = result.getValue(0,"result");
					if(str != null){
						float scores = Float.parseFloat(result.getValue(0,"result"));
						if(scores == 0){
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", 0);
							bo.set("score", scores);
							store.add(bo);
						}else{
							BusiObject bo = new BusiObject();
							bo.set("Assessment", typename);
							bo.set("Top_score", topscore);
							bo.set("score", scores);
							store.add(bo);
						}
					}else{
						BusiObject bo = new BusiObject();
						bo.set("Assessment", typename);
						bo.set("Top_score", 0);
						bo.set("score", 0);
						store.add(bo);
					}
				}else{
					BusiObject bo = new BusiObject();
					bo.set("Assessment", typename);
					bo.set("Top_score", 0);
					bo.set("score", 0);
					store.add(bo);
				}
			}
			
		});
	}
	
	public Date dateFormat(String date){
		Date df_date = new Date();
		String[] strdates = date.split("-");
		int year = Integer.parseInt(strdates[0]);
		int month = Integer.parseInt(strdates[1]);
		df_date.setYear(year-1900);
		df_date.setMonth(month-1);
		return df_date;
	}
	
	public void createData(String sectionCode,final String time){
		String sql = "select typeCode,topScore,keyDate,typeName,checkType from hr_TrainingTypeDefine order by typeCode";
		GreetingServiceAsync greetingservice = GWT.create(GreetingService.class);
		greetingservice.getDataSet(sql, new AsyncCallback<DataSet>(){
			@Override
			public void onFailure(Throwable caught) {
				MessageBox.alert("Error"," 服务器繁忙......", null);
			}
			@Override
			public void onSuccess(DataSet result) {
				
				BusiObject bom = new BusiObject();
				bom.set("YearMonth",dateFormat(time));
				bom.set("sectionCode", tree_depcode);
				mainstore.add(bom);
				
				for(int i = 0;i < result.getRowCount();i++){
					String typecode = result.getValue(i, "typeCode");
					int topscore = Integer.parseInt(result.getValue(i, "topScore"));
					String keyDate = result.getValue(i, "keyDate");
					String checktype = result.getValue(i, "checkType");
					String typeName = result.getValue(i, "typeName");
					
					if(checktype.equals("出勤率")){
						percentageAttendence(typecode,keyDate,tree_depcode,time,topscore,typeName);
					}
					if(checktype.equals("评分")){
						score(keyDate,tree_depcode,typecode,topscore,typeName,time);
					}
					if(checktype.equals("完成率")){
						percentageComplete(keyDate,tree_depcode,typecode,topscore,typeName,time);
					}
				}
			}
			
		});
	}
	
	private boolean  command(String sql){
		CommandSyncsql csql = new CommandSyncsql();//执行sql命令
		String delsql =sql;
		csql.getV().add(delsql);
		if(csql.getV().size()<=0){
			return false;
		}else{
			CommandSyncContainer list = new CommandSyncContainer();//可以添加的命令
			 list.add(csql);
		
			 GreetingServiceAsync greetingService = GWT
				.create(GreetingService.class);
			 greetingService
				.SendCommandSync(
						list,
						new AsyncCallback<CommandSyncReturnObject>() {
							@Override
							public void onFailure(
									Throwable caught) {
								MessageBox.alert("错误",
										"网络连接失败", null);
							}
							@Override
							public void onSuccess(
									CommandSyncReturnObject result) {
								if (result.isB()) {
									Info.display("提示信息", "添加成功");								
								}
								else{
									MessageBox.alert("错误",
											"错误，错误代码"+result.getMessage(), null);
									
								}
							}
						});
		}
		return true;
	}	
}
